﻿using Entity;
using IRepository;
using Microsoft.Extensions.Configuration;
using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Dapper;
using System.Data;
using Microsoft.AspNetCore.Http;

namespace Repository
{
    public class OutboundManagementRepository : IOutboundManagementRepository
    {
        private readonly string _connectionString;


        
        private readonly IHttpContextAccessor _httpContextAccessor;


        public OutboundManagementRepository(IConfiguration configuration,  IHttpContextAccessor httpContextAccessor)
        {
            // 从配置中读取连接字符串
            _connectionString = configuration.GetConnectionString("DefaultConnection");
            
            _httpContextAccessor = httpContextAccessor;

        }

        public async Task<int> AddOutboundOrderAsync(WMSKNZYDeliveryNote order, List<WMSKNZYOutboundDetails> details)
        {
            // 主表插入SQL
            string orderSql = @"
INSERT INTO wms_knzy_delivery_note (
    warehouse_id, type_id, outbound_order_no, associated_order_no,
    outbound_status, date_of_shipment, handler_id, note,
    create_by, create_time, is_delete
) VALUES (
    @warehouse_id, @type_id, @outbound_order_no, @associated_order_no,
    @outbound_status, @date_of_shipment, @handler_id, @note,
    @create_by, GETDATE(), @is_delete  -- 使用数据库时间函数自动生成创建时间
);
SELECT CAST(SCOPE_IDENTITY() AS INT);"; 

    // 明细表插入SQL（关联主表ID）
    string detailsSql = @"
INSERT INTO wms_knzy_outbound_details (
    delivery_order_id, product_code, curr_stk_out_num,
    selling_unit_price, note, create_by, create_time,
    saleamount, is_delete
) VALUES (
    @delivery_order_id, @product_code, @curr_stk_out_num,
    @selling_unit_price, @note, @create_by, GETDATE(),  -- 明细创建时间也使用数据库时间
    @saleamount, @is_delete
);";

            using (var conn = new SqlConnection(_connectionString))
            {
                await conn.OpenAsync();
                using (var transaction = conn.BeginTransaction())  // 开启事务（默认隔离级别）
                {
                    try
                    {
                        // 1. 新增出库单主表，获取主表ID
                        int deliveryOrderId = await conn.QuerySingleAsync<int>(
                            orderSql,
                            order,  // 自动映射实体参数
                            transaction
                        );

                        // 2. 遍历所有明细，关联主表ID后插入
                        foreach (var detail in details)
                        {
                            detail.delivery_order_id = deliveryOrderId;  // 绑定主表ID
                            await conn.ExecuteAsync(
                                detailsSql,
                                detail,  // 自动映射明细参数
                                transaction
                            );
                        }

                        // 所有操作成功，提交事务
                        transaction.Commit();
                        return deliveryOrderId;  // 返回主表ID
                    }
                    catch (Exception ex)
                    {
                        // 任意步骤失败，回滚所有操作
                        transaction.Rollback();
                        throw new Exception("新增出库单及明细失败：" + ex.Message);
                    }
                }
            }
        }

        public async Task AuditAsync(OutboundAuditDTO dto)
        {
            using var conn = new SqlConnection(_connectionString);
            await conn.OpenAsync();
            using var transaction = conn.BeginTransaction();
            try
            {
                var auditor = dto.reviewer;
                var now = DateTime.Now;

                // 查询待审核的出库单主表记录
                var sqlOutOrder = "SELECT * FROM wms_knzy_delivery_note WHERE outbound_order_no IN @OrderNos";
                var outOrders = await conn.QueryAsync<WMSKNZYDeliveryNote>(
                    sqlOutOrder,
                    new { OrderNos = dto.ordernos },
                    transaction
                );

                foreach (var order in outOrders)
                {
                    // 直接根据审核结果设置状态，不进行复杂判断
                    string status = dto.audit_result == "1" ? "4" : "3"; // 1-通过:4-已完成, 其他:3-驳回

                    if (dto.audit_result == "1")
                    {
                        // 审核通过时，需要执行库存扣减和销售订单更新
                        var sqlOutDetail = "SELECT * FROM wms_knzy_outbound_details WHERE delivery_order_id = @DeliveryOrderId";
                        var outDetails = await conn.QueryAsync<WMSKNZYOutboundDetails>(
                            sqlOutDetail,
                            new { DeliveryOrderId = order.delivery_order_id },
                            transaction
                        );

                        // 关联销售订单
                        var sqlSaleOrder = "SELECT * FROM wms_order_sales_order WHERE sales_order_no = @AssociatedNo";
                        var saleOrder = await conn.QueryFirstOrDefaultAsync<WMSOrderSalesOrder>(
                            sqlSaleOrder,
                            new { AssociatedNo = order.associated_order_no },
                            transaction
                        );

                        if (saleOrder != null)
                        {
                            // 查询销售订单明细
                            var sqlSaleDetail = "SELECT * FROM wms_order_sales_order_details WHERE sales_order_id = @SalesOrderId";
                            var saleDetails = await conn.QueryAsync<WMSOrderSalesOrderDetails>(
                                sqlSaleDetail,
                                new { SalesOrderId = saleOrder.sales_order_id },
                                transaction
                            );

                            // 更新销售明细已出库数量
                            foreach (var saleDetail in saleDetails)
                            {
                                var outQty = outDetails.FirstOrDefault(d => d.product_code == saleDetail.product_code)?.curr_stk_out_num ?? 0;
                                if (outQty <= 0) continue;

                                var sqlUpdateSaleDetail = @"
UPDATE wms_order_sales_order_details 
SET outbound_num = outbound_num + @OutQty 
WHERE sales_order_detail_id = @DetailId";
                                await conn.ExecuteAsync(
                                    sqlUpdateSaleDetail,
                                    new { OutQty = outQty, DetailId = saleDetail.sales_order_detail_id },
                                    transaction
                                );
                            }

                            // 重新查询更新后的销售明细，判断出库状态
                            var updatedSaleDetails = await conn.QueryAsync<WMSOrderSalesOrderDetails>(
                                sqlSaleDetail,
                                new { SalesOrderId = saleOrder.sales_order_id },
                                transaction
                            );

                            // 判断逻辑：所有明细的出库数量都大于等于计划数量，则为已完成(4)，否则为部分出库(7)
                            var allCompleted = updatedSaleDetails.All(d => d.outbound_num >= d.prod_num);
                            var newSaleStatus = allCompleted ? "4" : "7";

                            // 更新销售订单状态
                            var sqlUpdateSaleOrder = @"
UPDATE wms_order_sales_order 
SET document_status = @Status 
WHERE sales_order_id = @SalesOrderId";
                            await conn.ExecuteAsync(
                                sqlUpdateSaleOrder,
                                new { Status = newSaleStatus, SalesOrderId = saleOrder.sales_order_id },
                                transaction
                            );

                            // 关键修改：如果销售订单已完成，更新所有关联的出库单状态为"已完成"
                            if (allCompleted)
                            {
                                // 查询所有关联此销售订单的出库单
                                var sqlAllOutboundOrders = @"
SELECT * FROM wms_knzy_delivery_note 
WHERE associated_order_no = @SalesOrderNo AND outbound_status IN ('7', '4')";
                                var allOutboundOrders = await conn.QueryAsync<WMSKNZYDeliveryNote>(
                                    sqlAllOutboundOrders,
                                    new { SalesOrderNo = order.associated_order_no },
                                    transaction
                                );

                                // 将所有关联的出库单状态更新为"已完成"
                                foreach (var outboundOrder in allOutboundOrders)
                                {
                                    var sqlUpdateAllOutbound = @"
UPDATE wms_knzy_delivery_note 
SET outbound_status = '4', 
    update_time = @Now
WHERE delivery_order_id = @DeliveryOrderId";
                                    await conn.ExecuteAsync(
                                        sqlUpdateAllOutbound,
                                        new { Now = now, DeliveryOrderId = outboundOrder.delivery_order_id },
                                        transaction
                                    );
                                }
                            }
                        }

                        // 扣减库存 - 按货品编号和仓库ID扣减（无论是否关联销售订单都要执行）
                        foreach (var outDetail in outDetails)
                        {
                            var warehouseId = order.warehouse_id;

                            var sqlInventory = @"
SELECT * FROM wms_knzy_inventory 
WHERE product_code = @ProductCode 
AND warehouse_id = @WarehouseId";
                            var inventory = await conn.QueryFirstOrDefaultAsync<InventoryEntity>(
                                sqlInventory,
                                new
                                {
                                    ProductCode = outDetail.product_code,
                                    WarehouseId = warehouseId
                                },
                                transaction
                            );
                            if (inventory == null)
                                throw new Exception($"货品编码【{outDetail.product_code}】在仓库【{warehouseId}】无库存记录");

                            if (inventory.current_inventory < outDetail.curr_stk_out_num)
                                throw new Exception($"货品【{outDetail.product_code}】在仓库【{warehouseId}】库存不足，当前库存：{inventory.current_inventory}，需出库：{outDetail.curr_stk_out_num}");

                            var sqlUpdateInventory = @"
UPDATE wms_knzy_inventory 
SET current_inventory = current_inventory - @OutQty
WHERE product_code = @ProductCode 
AND warehouse_id = @WarehouseId";
                            await conn.ExecuteAsync(
                                sqlUpdateInventory,
                                new
                                {
                                    OutQty = outDetail.curr_stk_out_num,
                                    ProductCode = outDetail.product_code,
                                    WarehouseId = warehouseId
                                },
                                transaction
                            );
                        }
                    }

                    // 更新当前出库单主表状态
                    var sqlUpdateOutOrder = @"
UPDATE wms_knzy_delivery_note 
SET outbound_status = @Status, 
    reviewer = @Reviewer, 
    review_time = @ReviewTime, 
    audit_result = @AuditResult,
    update_time = @Now,  
    audit_opinion = @AuditOpinion
WHERE outbound_order_no = @OrderNo";

                    await conn.ExecuteAsync(
                        sqlUpdateOutOrder,
                        new
                        {
                            Status = status,
                            Reviewer = auditor,
                            ReviewTime = now,
                            AuditResult = dto.audit_result,
                            Now = now,
                            OrderNo = order.outbound_order_no,
                            AuditOpinion = dto.audit_opinion ?? order.audit_opinion,
                        },
                        transaction
                    );
                }

                transaction.Commit();
            }
            catch (Exception ex)
            {
                transaction.Rollback();
                throw new Exception("审核失败：" + ex.Message);
            }
        }

        public async Task<List<WMSOrderSalesOrder3>> GetAllWMSOutboundAsync(string sales_order_no = null, string document_status = null, string customer_name = null, DateTime? sales_date = null, string sales_person = null, string sales_dept = null)
        {
            string sql = @" SELECT
    so.sales_order_id AS 'sales_order_id',
    so.sales_order_no AS 'sales_order_no',
    so.document_status AS 'document_status',
    so.sales_date AS 'sales_date',
    so.note AS 'note',
    create_user.user_name AS 'create_user_name',  -- 避免与u.user_name重名，修改别名
    so.create_by AS 'create_by',
    so.create_time AS 'create_time',
    so.reviewer AS 'reviewer',
    so.review_time AS 'review_time',
    c.customer_name AS 'customer_name',
    c.contact_person AS 'contact_person',
    c.contact_phone_number AS 'contact_phone_number',
    org.organization_name AS 'org_name',  -- 原组织名称，避免混淆
    u.user_name AS 'sales_person',  -- 明确为销售负责人姓名
    SUM(sod.prod_num) AS 'prod_num',
    SUM(sod.saleamount) AS 'saleamount',
    STRING_AGG(pi.product_name, ',') AS 'product_name',
    create_org.organization_name AS 'create_department',  -- 明确为创建部门
    sales_org.organization_name AS 'sales_department'  -- 新增：销售部门名称
FROM
    dbo.wms_order_sales_order so
JOIN dbo.wms_basic_customer c ON so.customer_id = c.customer_id
JOIN dbo.sys_org_organizations org ON so.organizations_id = org.organization_id
JOIN dbo.sys_org_user_management u ON so.person_in_charge = u.user_id
JOIN dbo.sys_org_organizations sales_org ON u.organization_id = sales_org.organization_id  -- 新增：关联销售部门
JOIN dbo.wms_order_sales_order_details sod ON so.sales_order_id = sod.sales_order_id
JOIN dbo.wms_basic_product_information pi ON sod.product_code = pi.product_code
JOIN dbo.sys_org_user_management create_user ON so.create_by = create_user.user_id
JOIN dbo.sys_org_organizations create_org ON create_user.organization_id = create_org.organization_id
WHERE 1=1
    AND so.is_delete = 0
      -- 新增：状态必须为 6（未出库）或 7（部分出库）
    AND so.document_status IN (6, 7)
    -- 原条件保留
    AND (@SalesOrderNo IS NULL OR so.sales_order_no LIKE '%' + @SalesOrderNo + '%')  -- 模糊查询优化
    AND (@DocumentStatus IS NULL OR so.document_status = @DocumentStatus)
    AND (@CustomerName IS NULL OR c.customer_name LIKE '%' + @CustomerName + '%')  -- 模糊查询优化
    AND (@SalesDate IS NULL OR so.sales_date = @SalesDate)
    -- 新增：销售负责人（姓名模糊查询）
    AND (@SalesPerson IS NULL OR u.user_name LIKE '%' + @SalesPerson + '%')
    -- 新增：销售部门（部门名称模糊查询）
    AND (@SalesDept IS NULL OR sales_org.organization_name LIKE '%' + @SalesDept + '%')
GROUP BY
    so.sales_order_id,
    so.sales_order_no,
    so.document_status,
    so.sales_date,
    so.note,
    so.create_by,
    create_user.user_name,
    so.create_time,
    so.reviewer,
    so.review_time,
    c.customer_name,
    c.contact_person,
    c.contact_phone_number,
    org.organization_name,
    u.user_name,
    create_org.organization_name,
    sales_org.organization_name  -- 新增：销售部门分组
ORDER BY so.create_time DESC
";


            // 使用Dapper执行查询（需确保已注入IDbConnection）
            using (var connection =new SqlConnection(_connectionString) ) // 假设_dbConnection是数据库连接对象
            {
                var parameters = new
                {
                    SalesOrderNo = sales_order_no,
                    DocumentStatus = document_status,
                    CustomerName = customer_name,
                    SalesDate = sales_date,
                    SalesPerson = sales_person,
                    SalesDept = sales_dept
                };

                return (await connection.QueryAsync<WMSOrderSalesOrder3>(sql, parameters))
                    .ToList();
            }



        }

        public async Task<int> GetByLocationAndProduct(int locationId)
        {
            string sql = "SELECT current_inventory " +
                   "FROM wms_knzy_inventory " +
                   "WHERE location_id = @location  AND is_delete = 0";

            using (var conn = new SqlConnection(_connectionString))
            {
                // 关键修正：参数名必须与SQL中的@location保持一致
                var currentInventory = await conn.QueryFirstOrDefaultAsync<int?>(
                    sql,
                    new { location = locationId } // 匿名对象的属性名"location"对应SQL中的@location
                );

                // 处理空值：如果查询不到数据，返回0
                return currentInventory ?? 0;
            }

        }

        public async Task<List<StorageLocationEntity2>> GetByWarehouseId(int warehouseId)
        {
            string sql = "SELECT location_id, warehouse_id, location_number, location_name " +
                          "FROM wms_basic_storage_location " +
                          "WHERE warehouse_id = @warehouse_id AND is_delete = '0'";
            using (var conn = new SqlConnection(_connectionString))
            {
                // 正确写法（参数名与SQL中的@sales_order_no保持一致）
                var result = await conn.QueryAsync<StorageLocationEntity2>(sql, new { warehouse_id = warehouseId });
                return result.ToList();
            }
        }

        // 2. 实现方法调整
        public async Task<int> GetInventoryByTargetSql(int warehouseId, int locationId, string productCode)
        {
            var sql = @"
        SELECT 
            i.current_inventory  -- 只返回库存数值
        FROM 
            dbo.wms_basic_storage_location s
        INNER JOIN 
            dbo.wms_knzy_inventory i ON s.location_id = i.location_id
        INNER JOIN 
            dbo.wms_basic_product_information p ON i.product_code = p.product_code
        WHERE 
            s.warehouse_id = @warehouseId  -- 仓库ID
            AND s.location_id = @locationId  -- 库位ID
            AND i.product_code = @productCode  -- 货品编号（关键：筛选特定货品）
            AND i.is_delete = 0
            AND s.is_delete = '0'
            AND p.is_delete = '0';
    ";

            using (var conn = new SqlConnection(_connectionString))
            {
                // 查询指定条件的库存，无数据时返回0
                var inventory = await conn.QueryFirstOrDefaultAsync<int?>(
                    sql,
                    new { warehouseId, locationId, productCode }  // 传递三个参数
                );
                return inventory ?? 0;  // 确保返回int类型
            }
        }

        public async Task<List<WMSOrderSalesOrder4>> GetOutboundById(string salesOrderNo)
        {
            var sql = @"  SELECT 
    sod.sales_order_detail_id AS sales_order_detail_id,
    p.product_code AS product_code,
    p.product_name AS product_name,
    p.specification_model AS specification_model,
    d.DictLabel AS unit,
    sod.selling_unit_price AS selling_unit_price,
    sod.prod_num AS prod_num,
    sod.saleamount AS saleamount,
    sod.note AS note,
    sod.outbound_num AS outbound_num,
    -- 总库存 - 已出库数量 = 未出库数量
    (SUM(i.current_inventory) - sod.outbound_num) AS unoutbound,
    t.product_type_name AS product_type_name,
    SUM(i.current_inventory) AS current_inventory  -- 所有库位的库存总和
FROM 
    dbo.wms_order_sales_order_details sod
INNER JOIN dbo.wms_order_sales_order so 
    ON sod.sales_order_id = so.sales_order_id 
INNER JOIN dbo.wms_basic_product_information p 
    ON sod.product_code = p.product_code
INNER JOIN dbo.wms_basic_type_of_goods t 
    ON p.product_type_id = t.product_type_id
INNER JOIN dbo.wms_Dictionary_data d 
    ON p.unit = d.DictValue AND d.DictTypeCode = 'Unit'
INNER JOIN dbo.wms_knzy_inventory i 
    ON p.product_code = i.product_code
WHERE 
    sod.is_delete = '0' 
    AND so.sales_order_no = @sales_order_no
GROUP BY
sod.sales_order_detail_id,
    p.product_code,
    p.product_name,
    p.specification_model,
    d.DictLabel,
    sod.selling_unit_price,
    sod.prod_num,
    sod.saleamount,
    sod.note,
    sod.outbound_num,
    t.product_type_name;";

            using (var conn = new SqlConnection(_connectionString))
            {
                // 正确写法（参数名与SQL中的@sales_order_no保持一致）
                var result = await conn.QueryAsync<WMSOrderSalesOrder4>(sql, new { sales_order_no = salesOrderNo });
                return result.ToList();
            }
        }

        public async Task<List<OutboundOrderDTO>> GetOutboundOrders(string outboundOrderNo = null, string outboundType = null, string outboundStatus = null, string warehouseName = null)
        {
            string sql = @"SELECT 
    d.delivery_order_id AS delivery_order_id,
    d.outbound_order_no AS outbound_order_no,
    dt.DictLabel AS dictLabel,
    d.outbound_status AS outbound_status,
    d.date_of_shipment AS date_of_shipment,
    wh.warehouse_name AS warehouse_name,
    d.associated_order_no AS associated_order_no,
    c.customer_name AS customer_name,
    c.contact_person AS contact_person,
    c.contact_phone_number AS contact_phone_number,
    STRING_AGG(p.product_name, ',') AS product_name,
    SUM(od.curr_stk_out_num) AS curr_stk_out_num,  
    SUM(od.saleamount) AS saleamount,       
    handler.user_name AS handler_user_name,
    creator.user_name AS creator_user_name,
    org.organization_name AS organization_name,
    d.create_time AS create_time,
    d.reviewer AS approver_user_name,
    d.review_time AS update_time
FROM 
    dbo.wms_knzy_delivery_note d
LEFT JOIN 
    dbo.wms_knzy_outbound_details od ON d.delivery_order_id = od.delivery_order_id
LEFT JOIN 
    dbo.wms_order_sales_order so ON d.associated_order_no = so.sales_order_no  
LEFT JOIN 
    dbo.wms_basic_customer c ON so.customer_id = c.customer_id  
LEFT JOIN 
    dbo.wms_basic_product_information p ON od.product_code = p.product_code
LEFT JOIN 
    dbo.sys_org_user_management creator ON d.create_by = creator.user_id
LEFT JOIN 
    dbo.sys_org_organizations org ON creator.organization_id = org.organization_id
LEFT JOIN 
    dbo.sys_org_user_management handler ON d.handler_id = handler.user_id
LEFT JOIN 
    dbo.wms_Dictionary_data dt ON dt.DictTypeCode = 'StockoutType' AND dt.DictValue = d.type_id
LEFT JOIN 
    dbo.wms_basic_warehouse wh ON d.warehouse_id = wh.warehouse_id
WHERE 
    1 = 1  
    -- 出库单号筛选（支持模糊查询，如传入 'CK2025%'）
    AND (@outboundOrderNo IS NULL OR d.outbound_order_no LIKE @outboundOrderNo)
    -- 出库类型筛选（精确匹配）
    AND (@outboundType IS NULL OR dt.DictLabel = @outboundType)
    -- 单据状态筛选（精确匹配）
    AND (@outboundStatus IS NULL OR d.outbound_status = @outboundStatus)
    -- 仓库名称筛选（精确匹配）
    AND (@warehouseName IS NULL OR wh.warehouse_name = @warehouseName)
GROUP BY 
    d.delivery_order_id,
    d.outbound_order_no,
    dt.DictLabel,
    d.outbound_status,
    d.date_of_shipment,
    wh.warehouse_name,
    d.associated_order_no,
    c.customer_name,
    c.contact_person,
    c.contact_phone_number,
    handler.user_name,
    creator.user_name,
    org.organization_name,
    d.create_time,
    d.reviewer,
    d.review_time
-- 新增：按创建时间倒序排序（最新的记录排在前面）
ORDER BY d.create_time DESC;
    ";

            using (SqlConnection connection = new SqlConnection(_connectionString))
            {
                // 打开数据库连接（Dapper会自动打开，此处显式打开更清晰）
                connection.Open();

                // 传递参数（匿名对象的键需与SQL中的@参数名一致）
                var parameters = new
                {
                    outboundOrderNo,
                    outboundType,
                    outboundStatus,
                    warehouseName
                };

                // 执行查询并映射到DTO集合
                var result = await connection.QueryAsync<OutboundOrderDTO>(sql, parameters);

                // 转换为List并返回
                return result.AsList();
            }


        }


    }
}
